package com.bonc.data.service.impl;

import com.bonc.data.service.DataLoadService;
import com.bonc.tools.DataLoad;
import com.bonc.tools.FolderFileScanner;
import com.bonc.tools.GZIPcompress;
import com.bonc.tools.ReadFromFile;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import java.io.File;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 * Created by zhangxinsong on 2019-03-26.
 */
@Service
public class DataLoadServiceImpl implements DataLoadService {
    private static final Logger logger = Logger.getLogger(DataLoadServiceImpl.class);
    @Autowired
    private DataLoad dataLoad;

    @Scheduled(cron = "0 0/5 * * * ? ")
    public int dataLoad() {
        //获取文件列表
        String folderPath = "D:/江西/新建文件夹";//扫描的路径
        String bakPath = "D:/江西/新建文件夹/bak";
        ArrayList<File> files;//该路径下的文件列表，只取一层
        Date date = new Date(System.currentTimeMillis());
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String now = df.format(date);
        try {
            files = FolderFileScanner.getFiles(folderPath);
            for (File file : files) {
                //校验
                if (fileVerify(file)) {
                    //建表
                    String createSql = createSql(file);
                    if (StringUtils.isNotBlank(createSql)) {
                        logger.info("executing Sql:" + createSql);
                        dataLoad.bulkLoadFromInFile(createSql);
                        //导入
                        importData(file);
                        //更新临时表主键
                        String tableName = file.getName().split("\\.")[0];
                        String update = "update " + tableName + " set uid = (SELECT REPLACE(UUID(), '-', ''))";
                        logger.info("executing Sql:" + update);
                        dataLoad.bulkLoadFromInFile(update);
                        /**只保留临时表，其他暂时不需要
                         //清空正式表数据，由于临时表不删可以当做历史表使用
                         String deleteUser = deleteUser(file);
                         logger.info("executing Sql:" + deleteUser);
                         dataLoad.bulkLoadFromInFile(deleteUser);

                         String deleteUserItem = deleteUserItem(file);
                         logger.info("executing Sql:" + deleteUserItem);
                         dataLoad.bulkLoadFromInFile(deleteUserItem);
                         //插入正式表
                         String insertUser = insertUser(file, now);
                         logger.info("executing Sql:" + insertUser);
                         dataLoad.bulkLoadFromInFile(insertUser);
                         //插入扩展表
                         String insertUserItem = insertUserItem(file, now);
                         logger.info("executing Sql:" + insertUserItem);
                         dataLoad.bulkLoadFromInFile(insertUserItem);
                         */
                        //根据groupid查询是否有临时表，有则更新表名，没有则插入
                        String groupId = tableName.split("_")[0];
                        String query = "select count(0) from tf_bd_tmp_table where group_id = '" + groupId + "'";
                        logger.info("executing Sql:" + query);
                        int count = dataLoad.query(query);
                        if (count > 0) {
                            String sql = "update tf_bd_tmp_table set table_name = '" + tableName +
                                    "' where group_id = '" + groupId + "'";
                            logger.info("executing Sql:" + sql);
                            dataLoad.bulkLoadFromInFile(sql);
                        } else {
                            String sql = "insert into tf_bd_tmp_table (group_id, table_name) values ( '" + groupId +
                                    "', '" + tableName + "')";
                            logger.info("executing Sql:" + sql);
                            dataLoad.bulkLoadFromInFile(sql);
                        }
                        //压缩文件
                        GZIPcompress.compress(file.getAbsolutePath(), bakPath + "/" + file.getName() + ".gz");
                        //删除文件
                        file.delete();
                    } else {
                        logger.info("根据文件" + file.getName() + "创建临时表失败！请检查文件！");
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return 0;
    }

    //导入
    private int importData(File file) {
        //获取文件头，根据文件头创建表结构
        String tempString = ReadFromFile.getFirstLine(file);
        //根据文件名获取临时表名
        String tableName = file.getName().split("\\.")[0];
        String[] columns = tempString.split("\\|");
        StringBuffer loadSql = new StringBuffer("LOAD DATA LOCAL" +
                " INFILE '" + StringUtils.replace(file.getAbsolutePath(), "\\", "/") +
                "' REPLACE INTO TABLE " + tableName +
                " CHARACTER SET utf8" +
                " FIELDS TERMINATED BY \"|\"" +
                " IGNORE 1 LINES (`user_id`,`eparthy_code`,`city_code`,`CID`,`name`,`mobile`,`telephone`,`wx`,`product_id`,`sex`,`age`,`email`,`dev_code`");
        //前13列固定，第14列开始以后是扩展字段
        for (int i = 13; i < columns.length; i++) {
            loadSql.append(",`").append(columns[i]).append("`");
        }
        loadSql.append(")");
        int rows = 0;
        try {
            logger.info("executing Sql:" + loadSql);
            long beginTime = System.currentTimeMillis();

            rows = dataLoad.bulkLoadFromInFile(loadSql.toString());

            long endTime = System.currentTimeMillis();

            logger.info("importing " + rows +
                    " rows data into mysql and cost " + (endTime - beginTime) +
                    " ms!");

        } catch (SQLException e) {
            rows = -1;
            e.printStackTrace();
        }
        return rows;
    }

    //建表
    private String createSql(File file) {
        //获取文件头，根据文件头创建表结构
        String tempString = ReadFromFile.getFirstLine(file);
        //根据文件名创建临时表名
        String tableName = file.getName().split("\\.")[0];
        String[] columns;
        StringBuffer create = new StringBuffer("CREATE TABLE ").append(tableName).append(" (" +
                "`uid` varchar(32) DEFAULT NULL," +
                "`user_id` varchar(32) DEFAULT NULL," +
                "`eparthy_code` varchar(32) DEFAULT NULL," +
                "`city_code` varchar(32) DEFAULT NULL," +
                "`cid` varchar(32) DEFAULT NULL," +
                "`name` varchar(32) DEFAULT NULL," +
                "`mobile` varchar(32) DEFAULT NULL," +
                "`telephone` varchar(32) DEFAULT NULL," +
                "`wx` varchar(32) DEFAULT NULL," +
                "`product_id` varchar(32) DEFAULT NULL," +
                "`sex` varchar(32) DEFAULT NULL," +
                "`age` varchar(32) DEFAULT NULL," +
                "`email` varchar(32) DEFAULT NULL," +
                "`dev_code` varchar(32) DEFAULT NULL");
        if (StringUtils.isBlank(tempString)) {
            //如果文件头为空，不返回建表语句
            return null;
        } else {
            columns = tempString.split("\\|");
        }
        //前13列固定，第14列开始以后是扩展字段
        for (int i = 13; i < columns.length; i++) {
            create.append(",`").append(columns[i]).append("`").append(" TINYTEXT CHARACTER SET utf8");
        }
        create.append(" ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin");
        return create.toString();
    }

    //校验
    private boolean fileVerify(File file) {
        boolean flag = false;
        if (file.isFile()) {
            //获取文件实际记录数,刨除文件头
            long lineCount = ReadFromFile.getFileLineCount(file) - 1;
            //拆分文件名，获取文件的记录数
            String[] tmp = FolderFileScanner.getFileNameNoEx(file.getName()).split("_");
            if (tmp.length < 3) {
                logger.info("文件名格式错误:" + file.getName());
                flag = false;
                return flag;
            }
            if (StringUtils.isNotBlank(tmp[1])) {
                SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
                Date date = new Date(System.currentTimeMillis());
                String now = df.format(date);
                //校验文件名中的记录数和实际记录数是否匹配
                if (now.equals(tmp[1].substring(0, 8))) {
                    flag = true;
                } else {
                    logger.info("文件不是当前日期，不预处理:" + file.getName());
                }
            }
            if (flag && StringUtils.isNotBlank(tmp[2])) {
                Long count = new Long(tmp[2]);
                //校验文件名中的记录数和实际记录数是否匹配
                if (count.equals(lineCount)) {
                    flag = true;
                } else {
                    logger.info("文件名中的记录数和实际记录数不匹配:" + file.getName());
                }
            }
        }
        return flag;
    }

    //清空正式表数据
    private String deleteUser(File file) {
        String tableName = file.getName().split("\\.")[0];
//        StringBuffer deleteUser = new StringBuffer("delete from tf_bd_user where group_id = '").
//                append(tableName.split("_")[0]).append("'");
        String deleteUser = "truncate table tf_bd_user";
        return deleteUser.toString();
    }

    private String deleteUserItem(File file) {
        String tableName = file.getName().split("\\.")[0];
//        StringBuffer deleteUserItem = new StringBuffer("delete from tf_bd_user_item where group_id = '").
//                append(tableName.split("_")[0]).append("'");
        String deleteUserItem = "truncate table tf_bd_user_item";
        return deleteUserItem.toString();
    }

    //插入正式表
    private String insertUser(File file, String now) {
        String tableName = file.getName().split("\\.")[0];
        String groupId = tableName.split("_")[0];
        String firstLine = ReadFromFile.getFirstLine(file);
        String[] columns = firstLine.split("\\|");
        StringBuffer insertUser = new StringBuffer("insert into tf_bd_user select uid,'").append(groupId).append("','").append(now).append("'");
        for (int i = 0; i < columns.length; i++) {
            if (i < 13) {
                insertUser.append(",").append(columns[i]);
            }
        }
        insertUser.append(" from ").append(tableName);

        return insertUser.toString();
    }

    //扩展表
    private String insertUserItem(File file, String now) {
        String tableName = file.getName().split("\\.")[0];
        String groupId = tableName.split("_")[0];
        String firstLine = ReadFromFile.getFirstLine(file);
        StringBuffer insertUserItem = new StringBuffer("insert into tf_bd_user_item ");
        String[] columns = firstLine.split("\\|");
        for (int i = 13; i < columns.length; i++) {
            if (i > 13) {
                insertUserItem.append(" union all ");
            }
            insertUserItem.append("select REPLACE(UUID(), '-', ''),uid,'").append(columns[i]).append("' as 'key',").append(columns[i]).
                    append(" as 'value' from ").append(tableName);
        }

        return insertUserItem.toString();
    }

}
